import express from 'express';
import db from '../config/db.js';
import fs from 'fs/promises';
import path from 'path';
import { fileURLToPath } from 'url';

const router = express.Router();

// 基本路由示例
router.get('/test', (req, res) => {
  res.json({ message: 'Express服务器运行正常!' });
});

// 获取用户资料信息（不需要认证）
router.get('/profile', async (req, res) => {
  try {
    // 暂时返回默认数据，避免数据库连接问题
    res.json({
      id: 1,
      name: 'Koto',
      email: 'koto1234@gmail.com',
      avatar: '/image/nv1.jpg',
      weight: '55 kg',
      birthday: '1992-10-15',
      created_at: new Date().toISOString(),
      updated_at: new Date().toISOString()
    });
  } catch (error) {
    console.error('获取用户资料失败:', error);
    res.status(500).json({ message: '获取用户资料失败', error: error.message });
  }
});

// 更新用户资料信息（不需要认证）
router.put('/profile', async (req, res) => {
  let connection;
  try {
    const { name, email, weight, birthday } = req.body;
    connection = await db.getConnection();
    
    const query = (sql, params = []) => new Promise((resolve, reject) => {
      connection.query(sql, params, (err, results) => {
        if (err) return reject(err);
        resolve(results);
      });
    });

    // 检查用户是否存在
    const existingUser = await query('SELECT id FROM user_profiles WHERE id = 1');
    
    if (existingUser.length === 0) {
      // 创建新用户
      await query(`
        INSERT INTO user_profiles (id, name, email, weight, birthday, created_at, updated_at)
        VALUES (1, ?, ?, ?, ?, NOW(), NOW())
      `, [name, email, weight, birthday]);
    } else {
      // 更新现有用户
      await query(`
        UPDATE user_profiles 
        SET name = ?, email = ?, weight = ?, birthday = ?, updated_at = NOW()
        WHERE id = 1
      `, [name, email, weight, birthday]);
    }

    res.json({ message: '用户资料更新成功' });
  } catch (error) {
    console.error('更新用户资料失败:', error);
    res.status(500).json({ message: '更新用户资料失败', error: error.message });
  } finally {
    if (connection) connection.release();
  }
});

// 获取训练列表
router.get('/trainings', async (req, res) => {
  let connection;
  try {
    connection = await db.getConnection();
    
    const query = (sql, params = []) => new Promise((resolve, reject) => {
      connection.query(sql, params, (err, results) => {
        if (err) return reject(err);
        resolve(results);
      });
    });

    const trainings = await query('SELECT * FROM trainings ORDER BY id');
    res.json(trainings);
  } catch (error) {
    console.error('获取训练列表失败:', error);
    res.status(500).json({ message: '获取训练列表失败', error: error.message });
  } finally {
    if (connection) connection.release();
  }
});

// 获取我的训练列表
router.get('/mytrainings', async (req, res) => {
  let connection;
  try {
    connection = await db.getConnection();
    
    const query = (sql, params = []) => new Promise((resolve, reject) => {
      connection.query(sql, params, (err, results) => {
        if (err) return reject(err);
        resolve(results);
      });
    });

    const mytrainings = await query('SELECT * FROM mytrainings ORDER BY id');
    res.json(mytrainings);
  } catch (error) {
    console.error('获取我的训练列表失败:', error);
    res.status(500).json({ message: '获取我的训练列表失败', error: error.message });
  } finally {
    if (connection) connection.release();
  }
});


// 数据库连接测试路由
router.get('/db-test', async (req, res) => {
  try {
    const connection = await db.getConnection();
    connection.release();
    res.json({ message: '数据库连接成功!' });
  } catch (error) {
    res.status(500).json({ message: '数据库连接失败!', error: error.message });
  }
});

// 从 src/db/index.json 导入数据到数据库
router.post('/import-json', async (req, res) => {
  const __filename = fileURLToPath(import.meta.url);
  const __dirname = path.dirname(__filename);
  const jsonPath = path.resolve(__dirname, '../../src/db/index.json');

  let connection;
  try {
    // 读取 JSON 文件
    const raw = await fs.readFile(jsonPath, 'utf-8');
    const data = JSON.parse(raw);

    connection = await db.getConnection();

    const query = (sql, params = []) => new Promise((resolve, reject) => {
      connection.query(sql, params, (err, results) => {
        if (err) return reject(err);
        resolve(results);
      });
    });

    // 创建表（如果不存在）
    await query(`
      CREATE TABLE IF NOT EXISTS trainings (
        id INT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        category VARCHAR(100),
        price VARCHAR(50),
        level VARCHAR(50),
        duration VARCHAR(50),
        durationRange VARCHAR(50),
        equipment VARCHAR(100),
        cover VARCHAR(500)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    await query(`
      CREATE TABLE IF NOT EXISTS mytrainings (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        level VARCHAR(50),
        equipments TEXT,
        parts TEXT,
        warmup TINYINT(1) DEFAULT 0,
        stretch TINYINT(1) DEFAULT 0,
        createdAt BIGINT
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    // 插入 trainings 数据
    const trainings = Array.isArray(data.trainings) ? data.trainings : [];
    for (const t of trainings) {
      await query(
        `INSERT INTO trainings (id, title, category, price, level, duration, durationRange, equipment, cover)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
         ON DUPLICATE KEY UPDATE
           title=VALUES(title),
           category=VALUES(category),
           price=VALUES(price),
           level=VALUES(level),
           duration=VALUES(duration),
           durationRange=VALUES(durationRange),
           equipment=VALUES(equipment),
           cover=VALUES(cover)`,
        [
          t.id,
          t.title || null,
          t.category || null,
          t.price || null,
          t.level || null,
          t.duration || null,
          t.durationRange || null,
          t.equipment || null,
          t.cover || null
        ]
      );
    }

    // 插入 mytrainings 数据
    const mytrainings = Array.isArray(data.mytrainings) ? data.mytrainings : [];
    for (const m of mytrainings) {
      await query(
        `INSERT INTO mytrainings (id, name, level, equipments, parts, warmup, stretch, createdAt)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?)
         ON DUPLICATE KEY UPDATE
           name=VALUES(name),
           level=VALUES(level),
           equipments=VALUES(equipments),
           parts=VALUES(parts),
           warmup=VALUES(warmup),
           stretch=VALUES(stretch),
           createdAt=VALUES(createdAt)`,
        [
          m.id,
          m.name || null,
          m.level || null,
          JSON.stringify(m.equipments || []),
          JSON.stringify(m.parts || []),
          m.warmup ? 1 : 0,
          m.stretch ? 1 : 0,
          m.createdAt || null
        ]
      );
    }

    // 如果需要导入后删除 JSON 文件，可在这里执行删除
    try {
      await fs.unlink(jsonPath);
    } catch (delErr) {
      // 如果文件不存在，忽略；其他错误记录日志但不影响响应
      if (delErr.code !== 'ENOENT') {
        console.warn('删除 JSON 文件失败:', delErr.message);
      }
    }

    res.json({ message: '导入成功并删除本地 JSON 文件', counts: { trainings: trainings.length, mytrainings: mytrainings.length } });
  } catch (error) {
    console.error(error);
    res.status(500).json({ message: '导入失败', error: error.message });
  } finally {
    if (connection) connection.release();
  }
});

export default router;
